CREATE      procedure amsp_ICDemote
    @InSourceNodeID    numeric
AS
BEGIN

  /*
  ** DESCRIPTION:
  ** ------------
  ** Demotes a node in the Interest_Category tree (InSourceNodeID) to be a child of the next highest
  ** sibling node in the tree (same parent, and highest sort order less than the node being demoted).
  **
  ** INPUTS:
  ** -------
  **   @InSourceNodeID    Source node being demoted
  **
  ** OUTPUTS:
  ** -----------
  **   none
  **
  ** NOTES:
  ** ------
  **
  ** HISTORY:
  ** --------
  **   04/23/2003    N.Malhotra    Initial Version Created
  **
  */

  Declare  
    @DestSortOrder        numeric,
    @DestDepth            numeric,
    @DestID            numeric,
    @DestAncestorID        numeric,
    @SourceSortOrder        numeric,
    @SourceDepth        numeric,
    @SourceParentID        numeric

  BEGIN TRANSACTION

  /*
  ** Let's find out about our source node
  */

  SELECT
    @SourceSortOrder  = SortOrder,
    @SourceDepth      = CategoryDepth,
    @SourceParentID   = ParentCategoryID
  FROM
    Interest_Category
  WHERE
    InterestCategoryID = @InSourceNodeID

  /*
  ** Confirm the node can be demoted.  The node being demoted must have a sibling node
  ** above it in the tree (lower sort order but same parent).
  */

  SELECT @DestID = InterestCategoryID,
         @DestAncestorID = AncestorCategoryID,
         @DestDepth = CategoryDepth
    FROM Interest_Category
   WHERE SortOrder = (SELECT Max(SortOrder)
                        FROM Interest_Category
                       WHERE SortOrder < @SourceSortOrder
                         AND IsNULL(ParentCategoryID,0) = IsNULL(@SourceParentID,0))
  
  IF @@RowCount = 1 BEGIN

    /*
    ** Everything's a go.  Let's do it.
    */
   
    UPDATE Interest_Category
       SET ParentCategoryID = @DestID,
           AncestorCategoryID = @DestAncestorID
     WHERE InterestCategoryID = @InSourceNodeID

    /*
    ** amsp_FixTree will set the AncestorID, SortOrder, CategoryDepth for the entire tree.
    */
    
    EXEC amsp_ICFixTree

  END

  COMMIT TRANSACTION

END

GO
GRANT EXECUTE ON  [dbo].[amsp_ICDemote] TO [IMIS]
GO